﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace UtilityClassLIB
{
    public class UtilityClass
    {
        //AMHA - this is a class for code repeated in several classes.  You are welcome to add yours here too.

      
        public static string ConnectionString()
        {
            //String connectionString = @"Data Source=CALHAN;Initial Catalog=dbNascarEvent_test;Integrated Security=True";
            String connectionString = @"Data Source=.;Initial Catalog=dbNascarEvent_test;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
            return connectionString;
        }

        //-----------------------------Utility CODE ---------------------------------------

        public DataTable fillDataTable(string table)
        {
            DataTable dt = new DataTable(); //create new datatable
            SqlConnection connection = new SqlConnection(ConnectionString());//connect to database
            //select from database using table from method call 
            SqlCommand SelectCommand = new SqlCommand("Select * from " + table, connection);//select from database using table from method call

            connection.Open();
            dt.Load(SelectCommand.ExecuteReader()); //get database info read into datatable
            connection.Close();

            return dt; //return table to whatever called the method (in wpf class usually)
        }//end fillDataTable()



  // --------------------------extra stuff, might need 

        public string IsFound(DataRow[] row_result)
        {
            string result = ""; //sets result to empty

            if (row_result.Length > 0) // if there is a row
            {
                foreach (DataRow row in row_result) //loop through each row
                {
                    // Loop each item (like name, age so on) in the row and adds to previous item to form string result.
                    foreach (var item in row.ItemArray)
                    {
                        result = result + item + " "; // Prints each part of row + a space.
                    }
                }
            }
            else //if there is not a row
            {
                result = "The attribute you searched for was not found.";
            }

            return result;


        } //end isFound class

        public int GetAttributeIDfromName(string attributename)
        {
            int attributeID = 0;
            SqlConnection connection = new SqlConnection(ConnectionString());
            SqlCommand SelectCommand = new SqlCommand("Select AttributeID from tbAttributes where AttributeName = '" + attributename + "'", connection);
            connection.Open();

            attributeID = (Int32)SelectCommand.ExecuteScalar();

            connection.Close();

            return attributeID;
        }

        public int GetGroupIDfromName(string groupname)
        {
            int groupID = 0;
            SqlConnection connection = new SqlConnection(ConnectionString());
            SqlCommand SelectCommand = new SqlCommand("Select GroupID from tbGroups where GroupName = '" + groupname + "'", connection);
            connection.Open();

            groupID = (Int32)SelectCommand.ExecuteScalar();

            connection.Close();

            return groupID;

        }

        private int getUserIDfromUserName(string username)
        {
            int userid = 0;
            SqlConnection connection = new SqlConnection(ConnectionString());
            SqlCommand SelectCommand = new SqlCommand("Select userID from tbUsers where userName = '" + username + "'", connection);
            connection.Open();

            userid = (Int32)SelectCommand.ExecuteScalar();

            connection.Close();

            return userid;
        }


    }//end utility class
}//end namespace
